delete board
drop table board;
create table board(
	board_no number primary key,
	title varchar2(100) not null,
	content varchar2(2000) not null,
	w_date date not null,
	count number not null,
	category varchar2(20) not null,
	m_uid number not null,
	orgfilename varchar2(500),
	newfilename varchar2(500),
	constraint fk_board_id foreign key(m_uid) references member(m_uid)
);



drop sequence board_seq;
create sequence board_seq nocache;

insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'으히히히','첫글이다~',sysdate,0,'free',2);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'asd','aaa',sysdate,0,'free',3);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'sdf','!!!~',sysdate,0,'free',3);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'qwe','으히히',sysdate,0,'tip',5);

insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'sdf','으히히~',sysdate,0,'free',5);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'xcv','으히히~',sysdate,0,'free',6);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'wer','으히히~',sysdate,0,'free',7);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'cvb','으히히~',sysdate,0,'free',3);

insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'sdf','으히히~',sysdate,0,'free',2);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'wer','으히히~',sysdate,0,'tip',2);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'sdf','으히히~',sysdate,0,'tip',3);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'sdf','으히히~',sysdate,0,'tip',4);

insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'qwqe','으히히~',sysdate,0,'tip',4);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'dg','으히히~',sysdate,0,'tip',2);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'cvb','으히히~',sysdate,0,'free',5);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'sdf','으히히~',sysdate,0,'free',6);

insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'wer','으히히~',sysdate,0,'tip',7);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'cvb','으히히~',sysdate,0,'free',4);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'dfg','으히히~',sysdate,0,'free',3);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'wer','으히히~',sysdate,0,'tip',4);
insert into board(board_no,title,content,w_date,count,category,m_uid) values(board_seq.nextval,'dfg','으히히~',sysdate,0,'free',2);

select board_no,title,to_char(w_date,'yyyy.MM.DD')as w_date,count,m_uid from board
select * from board;
select * from member;


SELECT board_no,title,count,nickname,w_date,m_uid,category 
FROM(	 
	SELECT board_no,title,count,nickname,w_date,CEIL(rownum/5) AS page,m_uid,category   
FROM ( 
	SELECT board_no,title,count,nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m_uid,category 
FROM board b, member m ) 
		
	) WHERE PAGE='1'
    
    
	
	
--st1. nick
SELECT board_no,title,count,m_nickname,w_date,m_uid,category
FROM(
SELECT b.board_no,b.title,b.count,m.m_nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m.m_uid,b.category 
FROM board b, member m
where b.m_uid = m.m_uid and b.category='tip'
order by b.board_no desc
)

-- st2. 페이지별로 나누기
SELECT board_no,title,count,m_nickname,w_date,CEIL(rownum/5) AS page,m_uid,category   
FROM ( 
		SELECT b.board_no,b.title,b.count,m.m_nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m.m_uid,b.category 
		FROM board b, member m
		where b.m_uid = m.m_uid 
		order by b.board_no desc
	)
	
-- st3. 한페이지에 보여줄 게시물	
SELECT board_no,title,count,m_nickname,w_date,page,m_uid,category
FROM(	
SELECT board_no,title,count,m_nickname,w_date,CEIL(rownum/5) AS page,m_uid,category   
FROM ( 
		SELECT b.board_no,b.title,b.count,m.m_nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m.m_uid,b.category 
		FROM board b, member m
		where b.m_uid = m.m_uid 
		order by b.board_no desc )	
	) WHERE PAGE='1'	
	
	
-- st. 카테고리별 게시물
SELECT board_no,title,count,nickname,w_date,page,m_uid,category
FROM(	
SELECT board_no,title,count,m_nickname as nickname,w_date,CEIL(rownum/5) AS page,m_uid,category   
FROM ( 
		SELECT b.board_no,b.title,b.count,m.m_nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m.m_uid,b.category 
		FROM board b, member m
		where b.m_uid = m.m_uid and b.category='free'
		order by b.board_no desc )	
	) WHERE PAGE='1'
	
SELECT board_no,title,count,m_nickname,w_date,page,m_uid,category
FROM(	
SELECT board_no,title,count,m_nickname,w_date,CEIL(rownum/5) AS page,m_uid,category   
FROM ( 
		SELECT b.board_no,b.title,b.count,m.m_nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m.m_uid,b.category 
		FROM board b, member m
		where b.m_uid = m.m_uid and b.category='tip'
		order by b.board_no desc )	
	) WHERE PAGE='1'		
	
-- 수정	
update board set title='팁글 수정',content='수정했따!' where board_no='4' and category='tip'




select * from board where title ='륨아티스'

 select board_no,title,count,m.m_nickname,w_date,m.m_uid
 	 from board, member m
 	 where title = '륨아티스'



select *from board_reply
select *from member


SELECT board_no,title,count,nickname,w_date,page,m_uid,category,re_count 
FROM(	
SELECT board_no,title,count,m_nickname as nickname,w_date,CEIL(rownum/10) AS page,m_uid,category,re_count    
FROM ( 
		SELECT b.board_no,b.title,b.count,m.m_nickname,to_char(w_date,'yyyy.MM.DD')as w_date,m.m_uid,b.category,re_count,bno
		FROM board b, member m,(
			select bno,re_count from(
			select board_no as bno,max(restep)+1 as re_count from board_reply group by board_no
			)r
		)
		where b.m_uid = m.m_uid and b.category like '%free%' and bno(+)=b.board_no
		order by DECODE(b.category,'notice_free','1','notice_tip','2')asc,b.board_no desc )
	) WHERE PAGE=1